package com.jadcon.bedbreakfast.domain;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Component;

import com.jadcon.bedbreakfast.util.DbUtils;

@Component("reservationRepository")
public class ReservationRepositoryInMemoryImpl implements ReservationRepository {

	public List<Reservation> getReservationList() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT id, guestName, roomNumber, totalNumberOfGuests, checkIn, checkOut, address, city, state, zip, phoneNumber, email FROM reservation";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			System.out.println("Reservations were selected from database");
			return getReservationListFromResultSet(rs);
			
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, rs);
		}
		return null;
	}
	
	private void insert(Reservation reservation) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			String sql = "INSERT INTO reservation (roomNumber, totalNumberOfGuests, checkIn, checkOut, guestname, address, city, state, zip, phonenumber, email) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
	
			pstmt.setInt(1, reservation.getRoomNumber());
			pstmt.setInt(2, reservation.getTotalNumberOfGuests());
			pstmt.setDate(3, new java.sql.Date(reservation.getCheckIn().getTime()));
			pstmt.setDate(4, new java.sql.Date(reservation.getCheckOut().getTime()));
			pstmt.setString(5, reservation.getGuestName());
			pstmt.setString(6, reservation.getAddress1());
			pstmt.setString(7, reservation.getCity());
			pstmt.setString(8, reservation.getState());
			pstmt.setInt(9, reservation.getZip());
			pstmt.setString(10, reservation.getPhoneNumber());
			pstmt.setString(11, reservation.getEmail());
	
			pstmt.executeUpdate();
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, null);
		}

	}

	private void update(Reservation reservation) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			String sql = "UPDATE reservation SET roomNumber=?, totalNumberOfGuests=?, checkIn=?, checkOut=?, guestname=?, address=?, city=?, state=?, zip=?, phonenumber=?, email=? WHERE id=?";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
	
			pstmt.setInt(1, reservation.getRoomNumber());
			pstmt.setInt(2, reservation.getTotalNumberOfGuests());
			pstmt.setDate(3, new java.sql.Date(reservation.getCheckIn().getTime()));
			pstmt.setDate(4, new java.sql.Date(reservation.getCheckOut().getTime()));
			pstmt.setString(5, reservation.getGuestName());
			pstmt.setString(6, reservation.getAddress1());
			pstmt.setString(7, reservation.getCity());
			pstmt.setString(8, reservation.getState());
			pstmt.setInt(9, reservation.getZip());
			pstmt.setString(10, reservation.getPhoneNumber());
			pstmt.setString(11, reservation.getEmail());
	
			pstmt.executeUpdate();
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, null);
		}
	}

	public void save(Reservation reservation) {
		if (reservation.getReservationNumber() == 0) {
			insert(reservation);
		} else {
			update(reservation);
		}
	}

	public Reservation findByNumber(int reservationNumber) {

		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, guestName, address, city, state, zip, phoneNumber, email FROM reservation where id=?";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, reservationNumber);
			rs = pstmt.executeQuery();
			System.out.println("Reservation was selected from database");
	
			if (rs.next()) {
				return getReservationFromResultSet(rs);
			}
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, rs);
		}
		return null;
	}

	public List<Reservation> findReservationByRoom(int roomNumber) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {		
			String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, guestName, address, city, state, zip, phoneNumber, email FROM reservation where roomNumber=?";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, roomNumber);
			rs = pstmt.executeQuery();
			System.out.println("Reservation was selected from database");
			return getReservationListFromResultSet(rs);
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, rs);
		}
		return null;
	}

	public List<Reservation> findReservationByStartDate(Date checkIn) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {	
			String sql = "SELECT id, roomNumber, totalNumberOfGuests, checkIn, checkOut, guestName, address, city, state, zip, phoneNumber, email FROM reservation where checkIn=?";
			conn = DbUtils.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setDate(1, new java.sql.Date(checkIn.getTime()));
			rs = pstmt.executeQuery();
			System.out.println("Reservation was selected from database");
			return getReservationListFromResultSet(rs);
		} catch (SQLException e) {
			DbUtils.convertCheckedExceptionToRuntime(e);
		} finally {
			DbUtils.close(conn, pstmt, rs);
		}
		return null;
	}
	
	private List<Reservation> getReservationListFromResultSet(ResultSet rs) throws SQLException {
		List<Reservation> reservationList = new ArrayList<Reservation>();
		while (rs.next()) {
			Reservation reservation = getReservationFromResultSet(rs);
			reservationList.add(reservation);
		}
		return reservationList;
	}
	
	private Reservation getReservationFromResultSet(ResultSet rs) throws SQLException {
		int reservationNumber = rs.getInt("id");
		String guestName = rs.getString("guestName");
		int roomNumber = rs.getInt("roomNumber");
		int totalNumberOfGuests = rs.getInt("totalNumberOfGuests");
		Date checkIn = rs.getDate("checkIn");
		Date checkOut = rs.getDate("checkOut");
		String address = rs.getString("address");
		String city = rs.getString("city");
		String state = rs.getString("state");
		int zip = rs.getInt("zip");
		String phone = rs.getString("phoneNumber");
		String email = rs.getString("email");

		Reservation reservation = new Reservation(reservationNumber,
				roomNumber, totalNumberOfGuests, checkIn, checkOut,
				guestName, address, city, state, zip, phone, email);
		
		return reservation;
	}
}
